package db;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
import static dbmodel.Tables.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import java.util.logging.StreamHandler;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

/**
 *
 * @author Hans
 */
public final class DatabaseConnect {

    private Statement stmt;
    private Connection conn;
    private Properties properties;
    private Properties loadedProperties;
    private boolean connected;
    private String databaseURL;
    private final String dbname;
    private final String port;
    private final String hostIP;
    private final String user;
    private final String password;
    private DSLContext create;
    private static final Logger log = Logger.getLogger(DatabaseConnect.class.getName());

    public DatabaseConnect(String databaseName) {
        dbname = databaseName;
        port = "3306";
        hostIP = "localhost";
        user = "root";
        password = "root";
        properties = new Properties();
        connected = false;
        SimpleFormatter fmt = new SimpleFormatter();
        StreamHandler sh = new StreamHandler(System.out, fmt);
        log.addHandler(sh);
        log.setLevel(Level.ALL);
    }

    public DatabaseConnect(String databaseName, String port, String hostIP, String username, String password) {
        this.dbname = databaseName;
        this.port = port;
        this.hostIP = hostIP;
        this.user = username;
        this.password = password;
        properties = new Properties();
        connected = false;
    }

    public DatabaseConnect(String databaseName, String username, String password) {
        this.dbname = databaseName;
        this.user = username;
        this.password = password;
        port = "3306";
        hostIP = "localhost";
        properties = new Properties();
        connected = false;
    }

    public boolean checkDBExists(String dbName) throws ClassNotFoundException, SQLException {
        boolean result = false;
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Oprettelse af en forbindelse til databasen...");
        databaseURL = "jdbc:mysql://" + properties.getProperty("host/ip", hostIP) + ":" + properties.getProperty("port", port); //sammensætter url ud fra properties. har angivet defaults
        conn = DriverManager.getConnection(databaseURL, properties.getProperty("username", user), properties.getProperty("password", password)); //Open a connection
        ResultSet resultSet = conn.getMetaData().getCatalogs();
        while (resultSet.next()) {
            String databaseName = resultSet.getString(1);
            if (databaseName.equals(dbName)) {
                result = true;
                conn.close();
                databaseURL = "jdbc:mysql://" + properties.getProperty("host/ip", hostIP) + ":" + properties.getProperty("port", port) + "/" + properties.getProperty("database", dbName);
            }
        }
        return result;
    }

    public void connectToDB() throws SQLException, ClassNotFoundException {
        if (checkDBExists(properties.getProperty("database", dbname))) {
            conn = DriverManager.getConnection(databaseURL, user, password);
            System.out.println("Fandt databasen " + properties.getProperty("database", dbname));
            System.out.println(conn.getMetaData().getURL());
            stmt = conn.createStatement();
            create = DSL.using(conn, SQLDialect.MYSQL);
            connected = true;
        } else {
            System.out.println("Databasen findes ikke i MySQL serveren");
            throw new SQLException("Database does not exist");
        }
    }

    public Properties getProperties() {
        return properties;
    }

    public void checkToSave() {
        if (!properties.isEmpty() && !loadedProperties.isEmpty()) {
            if (!properties.getProperty("host/ip").equals(loadedProperties.getProperty("host/ip"))
                    || !properties.getProperty("port").equals(loadedProperties.getProperty("port"))
                    || !properties.getProperty("database").equals(loadedProperties.getProperty("database"))
                    || !properties.getProperty("username").equals(loadedProperties.getProperty("username"))
                    || !properties.getProperty("password").equals(loadedProperties.getProperty("password"))) {
                saveProperties();
            }
        }
    }

    public void checkProperties() {
        if (properties.isEmpty()
                || properties.getProperty("host/ip").isEmpty()
                || properties.getProperty("port").isEmpty()
                || properties.getProperty("database").isEmpty()
                || properties.getProperty("username").isEmpty()
                || properties.getProperty("password").isEmpty()) {
            properties.setProperty("host/ip", hostIP);
            properties.setProperty("port", port);
            properties.setProperty("database", dbname);
            properties.setProperty("username", user);
            properties.setProperty("password", password);
        }
    }

    /**
     * method for loading properties from an existing file. If file is not found
     * a new one will be created with default values.
     */
    public void loadProperties() {
        FileInputStream fileInputStream = null;
        try {
            String path = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
            String decodedPath = URLDecoder.decode(path, "UTF-8");
            String realPath = decodedPath.substring(0, decodedPath.lastIndexOf("/") + 1);
            fileInputStream = new FileInputStream(realPath + "config.properties");
            System.out.println("Prøver at indlæse den ønskede fil: " + realPath + "config.properties");
            properties.load(fileInputStream); //henter filen ind
            loadedProperties = (Properties) properties.clone();
        } catch (IOException ex) {
            System.out.println("Kan ikke finde den ønskede fil, prøver at oprette den.");
            saveProperties();
        } finally {
            try {
                if (fileInputStream != null) {
                    fileInputStream.close();
                }
            } catch (IOException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }

    public void saveProperties() {
        checkProperties();
        FileOutputStream output = null;
        try {
            String path = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
            String decodedPath = URLDecoder.decode(path, "UTF-8");
            String realPath = decodedPath.substring(0, decodedPath.lastIndexOf("/") + 1);
            output = new FileOutputStream(realPath + "config.properties");
            System.out.println("Gemmer indstillinger til database i den ønskede fil: " + realPath + "config.properties");
            properties.store(output, "Config for Mysql Database");
        } catch (IOException ex) {
            System.out.println(ex.getMessage());
        } finally {
            if (output != null) {
                try {
                    output.close();
                } catch (IOException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
    }

    public void saveProperties(Properties p) {
        properties = p;
        saveProperties();
    }

    public void setProperties(Properties p) {
        properties = p;
    }

    public void writeToDatabase(String sql) throws SQLException {
        stmt.execute(sql);
    }

    public ResultSet readFromDatabase(String sql) throws SQLException {
        return stmt.executeQuery(sql);
    }

    public void close() throws SQLException {
        System.out.println("Lukker forbindelse til databasen");
        if (stmt != null && !stmt.isClosed()) {
            stmt.close();
        }
        if (conn != null && !conn.isClosed()) {
            conn.close();
        }
    }

    public boolean isConnected() {
        return connected;

    }

    public String callShowDiscounts(String email, String pw, String prefix) throws SQLException {
        StringBuilder result = new StringBuilder();
        Result<Record> records = create
                .select()
                .from(DISCOUNT)
                .where(DISCOUNT.FK_CUSTOMER_EMAIL.eq(email))
                .fetch();
        for (Record r : records) {
            Integer discount_p = r.getValue(DISCOUNT.DISCOUNT_PERCENT);
            String name = r.getValue(DISCOUNT.FK_CATEGORY_NAME);
            String mail = r.getValue(DISCOUNT.FK_CUSTOMER_EMAIL);

            result.append("Procent: ").append(discount_p).append(" Kategori: ").append(name).append(" Mail: ").append(mail).append(System.lineSeparator());
        }
        return result.toString();
    }

    public String call(String procedure, String[] arguments, String prefix, boolean includeColumnLabel, boolean includeColumnType) throws SQLException {
        StringBuilder sql = new StringBuilder("call " + procedure + "(");
        char bling = 39; // '
        char comma = 44; // ;
        String newLine = System.lineSeparator(); // System specific New Line
        String error = "error:";
        for (int i = 0; i < arguments.length; i++) {
            String string = arguments[i];
            sql.append(bling).append(string).append(bling);
            if (i < arguments.length - 1) {
                sql.append(comma);
            }
        }
        sql.append(");");
        log.log(Level.INFO, sql.toString());
        StringBuilder result;
        try (ResultSet rs = readFromDatabase(sql.toString())) {
            result = new StringBuilder();
            rs.last();
            log.log(Level.INFO, "Rows: {0}", rs.getRow());
            rs.beforeFirst();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                if (rs.getString(1).equals(error)) {
                    result.append(rs.getString(2));
                } else {
                    for (int i = 1; i <= columnCount; i++) {
                        if (includeColumnLabel) {
                            result.append(rsmd.getColumnLabel(i)).append(": ");
                        }
                        result.append(rs.getString(i));
                        if (includeColumnType) {
                            result.append("; ").append(rsmd.getColumnTypeName(i));
                        }
                        if (i != columnCount) {
                            result.append(prefix);
                        }

                    }
                    result.append(newLine);
                }
            }
        }
        return result.toString();
    }
}
